package ssn.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import egovframework.rte.fdl.cmmn.exception.EgovBizException;

public class ExcelUploadUtil {
	
	private HSSFWorkbook wb;
	private XSSFWorkbook xwb;
	private int startRow;
	
	public ExcelUploadUtil(String filePath) throws Exception{
		this.init(filePath, 0);
	}
	
	public ExcelUploadUtil(String filePath, int startRow) throws Exception{
		this.init(filePath, startRow);
	}
	
	protected void init(String filePath, int startRow) throws Exception{
		this.startRow = startRow;
		File file = null;
		if(filePath.indexOf(".xlsx") > -1){
			file = new File(filePath);
			xwb = new XSSFWorkbook(new FileInputStream(file));
		}else if(filePath.indexOf(".xls") > -1){
			file = new File(filePath);
			POIFSFileSystem pfs = new POIFSFileSystem(new FileInputStream(file));
			wb = new HSSFWorkbook(pfs);
		}else{
			throw new EgovBizException("Excel 파일이 아닙니다.");
		}
	}
	
	public List<Map<String, String>> loadExcel(String[] cellInfoArray){
		if(xwb != null)
			return loadXSSFExcel(cellInfoArray);
		return loadHSSFExcel(cellInfoArray);
	}
	
	public List<Map<String, String>> loadXSSFExcel(String[] cellInfoArray){
		if(xwb == null){
			return null;
		}
		List<Map<String, String>> result = null;
		XSSFSheet sheet = xwb.getSheetAt(0); 			//시트 가져오기
		int rowCnt = sheet.getPhysicalNumberOfRows(); 	//Row 갯수 가져오기
		
		if ( rowCnt > startRow ) { 
			result = new ArrayList<Map<String,String>>();
		    for(int i=startRow; i<rowCnt; i++){ 
		    	XSSFRow row = sheet.getRow(i); 			//row 가져오기
		        Map<String, String> cellInfo = null;
		        if(row != null){
		            cellInfo = new HashMap<String, String>();
		            for(int c=0; c<cellInfoArray.length; c++){  // 정해진 데이터 이상을 입력했을때 무시
		            	XSSFCell cell = row.getCell(c); //cell 가져오기
		            	if(cell != null){
		                	cellInfo.put(cellInfoArray[c],cell.toString());
		                }else{
		                	cellInfo.put(cellInfoArray[c], "");
		                }
		            }
		        }
		        if(cellInfo != null){
		        	result.add(cellInfo);
		        }
		    }
		}
		return result;
	}
	
	public int loadExcelTotalCnt(){
		if(xwb != null)
			return xwb.getSheetAt(0).getPhysicalNumberOfRows()-startRow;
		return wb.getSheetAt(0).getPhysicalNumberOfRows()-startRow;
	}
	
	public List<Map<String, String>> loadHSSFExcel(String[] cellInfoArray){
		if(wb == null){
			return null;
		}
		List<Map<String, String>> result = null;
		HSSFSheet sheet = wb.getSheetAt(0); 			//시트 가져오기
		int rowCnt = sheet.getPhysicalNumberOfRows(); 	//Row 갯수 가져오기
		
		if ( rowCnt >= startRow ) { 
			result = new ArrayList<Map<String,String>>();
		    for(int i=startRow; i<rowCnt; i++){ 
		        HSSFRow row = sheet.getRow(i); 			//row 가져오기
		        Map<String, String> cellInfo = null;
		        if(row != null){
		            cellInfo = new HashMap<String, String>();
		            for(int c=0; c<cellInfoArray.length; c++){  // 정해진 데이터 이상을 입력했을때 무시
		                HSSFCell cell = row.getCell(c); //cell 가져오기
		                if(cell != null){
		                	cellInfo.put(cellInfoArray[c],cell.toString());
		                }else{
		                	cellInfo.put(cellInfoArray[c], "");
		                }
		            }
		        }
		        if(cellInfo != null){
		        	result.add(cellInfo);
		        }
		    }
		}
		return result;
	}
}
